package com.qibao.toolkit.inspector;

import com.qibao.toolkit.util.ArrayUtils;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import org.hibernate.resource.jdbc.spi.StatementInspector;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Collection;
import java.util.List;

public class SqlInspector2 implements StatementInspector {

    private final static Logger log = LoggerFactory.getLogger(SqlInspector2.class);

    private final static String INDETITY_KEY_COLUMN = "rating_request_id";
    private final static String INDETITY_KEY_FILED = "ratingrequestid";
    private final static String[] EXCLUSIONS = {"rating_request", "ratingrequestdo",
            "ratingrequest"};


    @Override
    public String inspect(String sql) {

        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            parse.accept(new StatementVisitorAdapter() {

                @Override
                public void visit(Select select) {
                    ckeckSelect(select);
                }

                @Override
                public void visit(Insert insert) {
                    checkInsert(insert);
                }

                @Override
                public void visit(Update update) {
                    checkUpdate(update);
                }

                @Override
                public void visit(Delete delete) {
                    checkDelete(delete);
                }
            });

        } catch (RuntimeException e) {
            throw e;
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
        return sql;
    }

    /**
     * 检查查询语句
     */
    private void ckeckSelect(Select select) {

        select.getSelectBody().accept(new SelectVisitorAdapter() {
            @Override
            public void visit(PlainSelect plainSelect) {

                // table
                Table table = (Table) plainSelect.getFromItem();

                //special tables
                if (isExclusion(table.getName())) {

                    specialTables(plainSelect.toString());

                    //others
                } else {

                    if (!validSelect(plainSelect)) {
                        throw new RuntimeException(
                                "invalid select statement, not found column rating_request_id : "
                                        + plainSelect.toString());
                    }
                }
            }
        });
    }

    private Boolean validSelect(PlainSelect plainSelect) {

        List<SelectItem> selectItems = plainSelect.getSelectItems();

        for (SelectItem selectItem : selectItems) {

            SelectExpressionItem it = (SelectExpressionItem) selectItem;
            Column column = (Column) it.getExpression();

            return eqColumn(column);
        }

        return Boolean.FALSE;
    }


    //检查插入语句
    private void checkInsert(Insert insert) {
        Table table = insert.getTable();

        //special tables
        if (isExclusion(table.getName())) {

            specialTables(insert.toString());

            //others
        } else {
            if (!validInsert(insert.getColumns())) {
                throw new RuntimeException(
                        "invalid insert statement, not found column rating_request_id : "
                                + insert.toString());
            }
        }
    }

    private Boolean validInsert(List<Column> columns) {
        for (Column column : columns) {
            return eqColumn(column);
        }
        return Boolean.FALSE;
    }


    //检查更新语句
    private void checkUpdate(Update update) {
        List<Table> tables = update.getTables();

        //special tables
        if (isExclusion(tables)) {

            specialTables(update.toString());

            //others
        } else {

            Expression where = update.getWhere();

            if (!validExpression(where)) {
                throw new RuntimeException(
                        "invalid update statement, not found column rating_request_id : "
                                + update.toString());
            }
        }
    }

    //检查删除语句
    private void checkDelete(Delete delete) {

        Table table = delete.getTable();

        //special table
        if (isExclusion(table.getName())) {

            specialTables(delete.toString());

            //others
        } else {

            Expression where = delete.getWhere();

            if (!validExpression(where)) {
                throw new RuntimeException(
                        "invalid delete statement, not found column rating_request_id : "
                                + delete.toString());
            }
        }


    }


    private Boolean validExpression(Expression expression) {

        if (expression instanceof Column) {
            Column column = (Column) expression;

            return eqColumn(column);

        } else if (expression instanceof AndExpression) {
            AndExpression expression2 = (AndExpression) expression;
            Boolean left = validExpression(expression2.getLeftExpression());
            Boolean right = validExpression(expression2.getRightExpression());

            return left && right;

        } else if (expression instanceof OrExpression) {
            OrExpression expression2 = (OrExpression) expression;

            Boolean left = validExpression(expression2.getLeftExpression());
            Boolean right = validExpression(expression2.getRightExpression());

            return left && right;

        } else if (expression instanceof EqualsTo) {
            EqualsTo expression2 = (EqualsTo) expression;

            return validExpression(expression2.getLeftExpression());
            //between
        } else if (expression instanceof Between) {
            return validExpression(((Between) expression).getLeftExpression());
            // in or not in
        } else if (expression instanceof InExpression) {
            return validExpression(((InExpression) expression).getLeftExpression());
            //like
        } else if (expression instanceof LikeExpression) {
            return validExpression(((LikeExpression) expression).getLeftExpression());
            // <
        } else if (expression instanceof MinorThan) {
            return validExpression(((MinorThan) expression).getLeftExpression());
            // <=
        } else if (expression instanceof MinorThanEquals) {
            return validExpression(((MinorThanEquals) expression).getLeftExpression());
            // is null
        } else if (expression instanceof IsNullExpression) {
            return validExpression(((IsNullExpression) expression).getLeftExpression());
            // >
        } else if (expression instanceof GreaterThan) {
            return validExpression(((GreaterThan) expression).getLeftExpression());
            // >=
        } else if (expression instanceof GreaterThanEquals) {
            return validExpression(((GreaterThanEquals) expression).getLeftExpression());
        }

        log.warn("sql inspector not hit any expression, " + expression.toString());
        return Boolean.FALSE;
    }


    private void specialTables(String sql) {

    }

    private Boolean isExclusion(String table) {
        return ArrayUtils.contains(EXCLUSIONS, table);
    }

    private Boolean isExclusion(Collection tables) {

        Boolean result = Boolean.FALSE;
        for (Object table : tables) {
            result = result || ArrayUtils.contains(EXCLUSIONS, table);
        }

        return result;
    }

    private Boolean eqColumn(Column column) {
        if (INDETITY_KEY_COLUMN.equalsIgnoreCase(column.getColumnName())
                || INDETITY_KEY_FILED.equalsIgnoreCase(column.getColumnName())) {
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }
}
